package com.java2007.hotel.dao.impl;

import com.java2007.hotel.dao.FoodDao;
import com.java2007.hotel.pojo.Food;
import com.java2007.hotel.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;


import java.sql.SQLException;
import java.util.List;

/**
 * @author csl
 * @version V1.0
 * @date 2021/01/23
 **/
public class FoodDaoImpl implements FoodDao {
    /**
     * 查询所有菜品
     *
     * @return
     */
    @Override
    public List<Food> selectAll() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT\n" +
                "  food_id id,\n" +
                "  food_name name,\n" +
                "  food_cuisine_id cuisineId,\n" +
                "  food_price price ,\n" +
                "  food_vip_price vipPrice,\n" +
                "  food_picture picture,\n" +
                "  food_introduce introduce,\n" +
                "  food_create_date createDate,\n" +
                "  food_update_date updateDate,\n" +
                "  food_creater_id createrId\n" +
                "FROM t_food";
        List<Food> query = queryRunner.query(sql, new BeanListHandler<Food>(Food.class));
        return query;
    }

    /**
     * 根据名字模糊查询
     *
     * @param name
     * @return
     */
    @Override
    public List<Food> selectFoodByName(String name) throws SQLException {
       name = "%"+name+"%";
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "SELECT\n" +
                "  food_id id,\n" +
                "  food_name name,\n" +
                "  food_cuisine_id cuisineId,\n" +
                "  food_price price ,\n" +
                "  food_vip_price vipPrice,\n" +
                "  food_picture picture,\n" +
                "  food_introduce introduce,\n" +
                "  food_create_date createDate,\n" +
                "  food_update_date updateDate,\n" +
                "  food_creater_id createrId\n" +
                "FROM t_food\n" +
                "WHERE food_name LIKE ?";
        List<Food> query = queryRunner.query(sql, new BeanListHandler<Food>(Food.class), name);
        return query;
    }

    /**
     * 根据名字模糊分页查询
     * @param name
     * @return
     */
    @Override
    public List<Food> selectFoodByNameLimit(String name, int start, int pageCount) throws SQLException {
        name = "%"+name+"%";
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "SELECT\n" +
                "  food_id id,\n" +
                "  food_name name,\n" +
                "  food_cuisine_id cuisineId,\n" +
                "  food_price price ,\n" +
                "  food_vip_price vipPrice,\n" +
                "  food_picture picture,\n" +
                "  food_introduce introduce,\n" +
                "  food_create_date createDate,\n" +
                "  food_update_date updateDate,\n" +
                "  food_creater_id createrId\n" +
                "FROM t_food\n" +
                "WHERE food_name LIKE ?" +
                " limit ?,?";
        List<Food> query = queryRunner.query(sql, new BeanListHandler<Food>(Food.class), name,start,pageCount);
        return query;
    }

    /**
     * 根据菜系id查询菜品
     * @param cuisineId
     * @return
     */
    @Override
    public List<Food> selectFoodBy(int cuisineId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "SELECT\n" +
                "  food_id id,\n" +
                "  food_name name,\n" +
                "  food_cuisine_id cuisineId,\n" +
                "  food_price price ,\n" +
                "  food_vip_price vipPrice,\n" +
                "  food_picture picture,\n" +
                "  food_introduce introduce,\n" +
                "  food_create_date createDate,\n" +
                "  food_update_date updateDate,\n" +
                "  food_creater_id createrId\n" +
                "FROM t_food\n" +
                "WHERE food_cuisine_id = ?";
        List<Food>  query = queryRunner.query(sql, new BeanListHandler<>(Food.class), cuisineId);
        return query;
    }

    /**
     * 根据名字查询一个
     *
     * @param name
     * @return
     */
    @Override
    public Food selectFoodByNameOne(String name) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "SELECT\n" +
                "  food_id id,\n" +
                "  food_name name,\n" +
                "  food_cuisine_id cuisineId,\n" +
                "  food_price price ,\n" +
                "  food_vip_price vipPrice,\n" +
                "  food_picture picture,\n" +
                "  food_introduce introduce,\n" +
                "  food_create_date createDate,\n" +
                "  food_update_date updateDate,\n" +
                "  food_creater_id createrId\n" +
                "FROM t_food\n" +
                "WHERE food_name = ?";
        Food query = queryRunner.query(sql, new BeanHandler<>(Food.class), name);
        return query;
    }

    /**
     * 根据id查询菜品
     *
     * @param id
     * @return
     */
    @Override
    public Food selectFoodById(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "SELECT\n" +
                "  food_id id,\n" +
                "  food_name name,\n" +
                "  food_cuisine_id cuisineId,\n" +
                "  food_price price ,\n" +
                "  food_vip_price vipPrice,\n" +
                "  food_picture picture,\n" +
                "  food_introduce introduce,\n" +
                "  food_create_date createDate,\n" +
                "  food_update_date updateDate,\n" +
                "  food_creater_id createrId\n" +
                "FROM t_food\n" +
                "WHERE food_id = ?";
        Food query = queryRunner.query(sql, new BeanHandler<>(Food.class), id);
        return query;
    }

    /**
     * 根据id删除菜品
     *
     * @param id
     * @return
     */
    @Override
    public int deleteFoodById(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "DELETE FROM t_food WHERE food_id =?";
        int update = queryRunner.update(JdbcUtils.getConnection(), sql, id);
        return update;
    }

    /**
     * 根据菜系id删除菜品
     * @param cuisineId
     * @return
     * @throws SQLException
     */
    @Override
    public int deleteFoodByCuisineId(int cuisineId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "DELETE FROM t_food WHERE food_cuisine_id =?";
        int update = queryRunner.update(JdbcUtils.getConnection(), sql, cuisineId);
        return update;
    }

    /**
     * 修改菜品
     *
     * @param food
     * @return
     */
    @Override
    public int updateFood(Food food) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "UPDATE t_food\n" +
                "SET food_id = ?,\n" +
                "  food_name = ?,\n" +
                "  food_cuisine_id = ?,\n" +
                "  food_price = ?,\n" +
                "  food_vip_price = ?,\n" +
                "  food_picture = ?,\n" +
                "  food_introduce = ?,\n" +
                "  food_update_date = ?,\n" +
                "  food_creater_id = ?\n" +
                "WHERE food_id = ?";
        int update = queryRunner.update(JdbcUtils.getConnection(), sql, food.getId(), food.getName(), food.getCuisineId(),
                food.getPrice(), food.getVipPrice(), food.getPicture(), food.getIntroduce(),
                food.getUpdateDate(), food.getCreaterId(), food.getId());
        return update;
    }

    /**
     * 增加一个菜品
     *
     * @param food
     * @return
     */
    @Override
    public int addFood(Food food) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "INSERT INTO t_food\n" +
                "            (food_name,\n" +
                "             food_cuisine_id,\n" +
                "             food_price,\n" +
                "             food_vip_price,\n" +
                "             food_picture,\n" +
                "             food_introduce,\n" +
                "             food_create_date,\n" +
                "             food_update_date,\n" +
                "             food_creater_id)\n" +
                "VALUES (?,?,?,?,?,?,?,?,?)";
        int update = queryRunner.update(JdbcUtils.getConnection(), sql, food.getName(), food.getCuisineId(),
                food.getPrice(), food.getVipPrice(), food.getPicture(), food.getIntroduce(), food.getCreateDate(),
                food.getUpdateDate(), food.getCreaterId());
        return update;
    }
    /**
     * 分页查询菜品
     * @param
     * @return
     */
    @Override
    public List<Food> selectLimit(int start, int pageSize) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "SELECT\n" +
                "  food_id id,\n" +
                "  food_name name,\n" +
                "  food_cuisine_id cuisineId,\n" +
                "  food_price price ,\n" +
                "  food_vip_price vipPrice,\n" +
                "  food_picture picture,\n" +
                "  food_introduce introduce,\n" +
                "  food_create_date createDate,\n" +
                "  food_update_date updateDate,\n" +
                "  food_creater_id createrId\n" +
                "FROM t_food\n" +
                "LIMIT ?,?";
        List<Food> query = queryRunner.query(sql, new BeanListHandler<>(Food.class), start, pageSize);
        return query;
    }

    /**
     * 根据菜系id分页查询菜品
     * @param cuisineId
     * @return
     */
    @Override
    public List<Food> selectFoodBy(int cuisineId, int start, int pageCount) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql =  "SELECT\n" +
                "  food_id id,\n" +
                "  food_name name,\n" +
                "  food_cuisine_id cuisineId,\n" +
                "  food_price price ,\n" +
                "  food_vip_price vipPrice,\n" +
                "  food_picture picture,\n" +
                "  food_introduce introduce,\n" +
                "  food_create_date createDate,\n" +
                "  food_update_date updateDate,\n" +
                "  food_creater_id createrId\n" +
                "FROM t_food\n" +
                "WHERE food_cuisine_id = ?" +
                " limit ?,?";
        List<Food>  query = queryRunner.query(sql, new BeanListHandler<>(Food.class), cuisineId,start,pageCount);
        return query;
    }
}
